import pymysql

class SQL_GJP(object):
    def __init__(self, host, user, password, database,port):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = port
        self.db = pymysql.connect(host=self.host, user=self.user, password=self.password, database=self.database, port=self.port)
        self.cursor = self.db.cursor()
    #查询所有数据
    def select_all_data(self, query):
        try:
            self.cursor.execute(query)
            data = self.cursor.fetchall()
            return data
        except Exception as e:
            print("Error occurred:", e)
            self.db.rollback()
    #按id查询数据
    def select_data_by_id(self, values):
        try:
            self.cursor.execute("SELECT * FROM gjp WHERE id=%s", values)
            data = self.cursor.fetchall()
        except Exception as e:
            print("Error occurred:", e)
            self.db.rollback()
        return data
    #按时间查询
    def select_data_by_time(self, values):
        try:
            self.cursor.execute("SELECT * FROM gjp WHERE time=%s", values)
            data = self.cursor.fetchall()
        except Exception as e:
            print("Error occurred:", e)
            self.db.rollback()
        return data
    #添加数据
    def insert_data(self,values):
        try:
            self.cursor.execute(
                "INSERT INTO gjp (id, type, account, money, time, shuoming) VALUES (%s, %s, %s, %s, %s, %s)", values)
            self.db.commit()
            return self.cursor.lastrowid
        except Exception as e:
            print("Error occurred:", e)
            self.db.rollback()
    #更新数据
    def update_data_by_id(self, values):
        try:
            self.cursor.execute("UPDATE gjp SET type=%s, account=%s, money=%s, time=%s, shuoming=%s WHERE id=%s", values)
            self.db.commit()
            return self.cursor.rowcount
        except Exception as e:
            print("Error occurred:", e)
            self.db.rollback()
    #删除数据
    def delete_data_by_id(self, id:int):
        try:
            self.cursor.execute("DELETE FROM gjp WHERE id=%s", id)
            self.db.commit()
            return self.cursor.rowcount
        except Exception as e:
            print("Error occurred:", e)
            self.db.rollback()

    def __del__(self):
        self.cursor.close()
        self.db.close()
